<?php
	/**
	 * Страница подсчета итогов.
	 * 
	 * @author Костин Алексей Васильевич aka Volt(220)
	 * @copyright Copyright (c) 2010, Костин Алексей Васильевич
	 * @license http://www.gnu.org/licenses/gpl-3.0.html GNU Public License
	 * @version 1.0
	 * @package HomeMoney
	 */

	/**
	 * Подключение настроек. 
	 */
	require_once("../ini.php");

	$DBCon=SQLDBFactory::getDB();

	$curTab=0;

	$sql="SELECT min(date) as С, max(date) as По FROM 
		(select date from prihod union select date from buys) as tab";
	$rez=$DBCon->select($sql, null, null, 1);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Промежуток времени";
	$curTab++;
	
	$sql="SELECT item as Товар, cat as Категория, round(sum(col),3) as Количество, round(sum(basket.sum),2) as Сумма
		FROM basket
		LEFT OUTER JOIN goods ON goods.id = basket.id_good
		LEFT OUTER JOIN categ ON goods.id_cat = categ.id
		group by item, cat
		order by item";
	$rez=$DBCon->select($sql);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Товары";
	$tabs[$curTab]->sumRows(4+8);
	$curTab++;
	
	$sql="SELECT parent.cat as Категория, round(sum(sum),2) as Сумма
		FROM basket
		LEFT OUTER JOIN goods ON goods.id = basket.id_good
		LEFT OUTER JOIN categ as ch ON goods.id_cat = ch.id
		LEFT OUTER JOIN categ_tree on categ_tree.id_cat=ch.id
		LEFT OUTER JOIN categ as parent ON categ_tree.id_par = parent.id
		group by id_par, parent.cat
		order by parent.cat";
	$rez=$DBCon->select($sql);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Категории";
	$curTab++;

	$sql="SELECT name as Магазин, addr as Адрес, round(sum(sum),2) as Сумма
		from buys 
		LEFT OUTER JOIN shops ON shops.id = buys.id_shop
		LEFT OUTER JOIN shops_address on shops_address.id_shop=shops.id
		LEFT OUTER JOIN address on address.id=shops_address.id_addr
		group by name, addr
		order by name";
	$rez=$DBCon->select($sql);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Магазины c адресами";
	$tabs[$curTab]->sumRows(4);
	$curTab++;

	$sql="SELECT name as Магазин, round(sum(sum),2) as Сумма
		from buys LEFT OUTER JOIN shops ON shops.id = buys.id_shop
		group by name
		order by name";
	$rez=$DBCon->select($sql);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Магазины";
	$tabs[$curTab]->sumRows(2);
	$curTab++;
	
	$DBCon->delete("delete from ostatok");
	$DBCon->insert("insert into ostatok(`sum`, `date`) 
		select p.vsego-b.vsego, now()
		from (select ifnull(sum(sum),0) as vsego from prihod) as p,
		(select  ifnull(sum(sum),0) as vsego from buys) as b");
	
	$sql="SELECT 'Получено' as Графа , round(sum(sum),2) as Всего
		FROM prihod
		union
		SELECT 'Потрачено' as Графа , round(sum(sum),2) as Всего
		FROM buys
		union
		(select 'Остаток' as Графа, round(`sum`,2) as Всего 
		from ostatok order by `date` desc limit 1)";
	$rez=$DBCon->select($sql, null, null, 2);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Итог";
	$curTab++;

	$sql="SELECT otkuda as Откуда , round(sum(sum),2) as Всего
		FROM prihod as pr join OtkudaMoney as om on pr.id_otkuda=om.id
		group by otkuda
		order by otkuda";
	$rez=$DBCon->select($sql);
	$tabs[$curTab]=new TableTpl($rez);
	$tabs[$curTab]->title= "Приход";
	$tabs[$curTab]->sumRows(2);
	$curTab++;

	$bodyTpl=new Template(DOCROOT."/Templates/tablesPrint.tpl");
	$bodyTpl->tables=$tabs;

	$pageT="Итого";
	include (DOCROOT."/pages.php"); 
?>